#!/usr/bin/perl
#
# A most un-DB-like application: Get historical data by rooting
# through the db .txt file
#
# 981001: Move ZZ and XX into the XW count
#
use Counter; # must drag this in to satisfy all dependencies...
use Validate::Common;

$Validate::Common::chatty = 0; # turn off error msgs

# Preliminary gunk: Try to tabulate persons' country per month, accounting
# for deletes (arg).

# translation of monthnames (correct ones)
%months = (
"Jan" => 1,
"Feb" => 2,
"Mar" => 3,
"Apr" => 4,
"May" => 5,
"Jun" => 6,
"Jul" => 7,
"Aug" => 8,
"Sep" => 9,
"Oct" => 10,
"Nov" => 11,
"Dec" => 12,
	   );


open(FILE, "db/persons.txt") || die "Unable to open db file\n";
getcorrections();

while (<FILE>) {
    chop;
    if (/^\/\//) {
	processentry();
	$id = $';
	$hasdata = 0;
    } elsif (/^country:/) {
	$country = $';
	$hasdata = 1;
    } elsif (/^recdate:/) {
	$date = $';
	$hasdata = 1;
    } elsif (/^email:/) {
	$email = $';
	$hasdata = 1;
    } else {
	$hasdata = 1;
    }
    ++ $linecount;
    #DEBUG
    #print STDERR "\rline $linecount" if ($linecount % 1000) == 0;
    #last if $linecount > 100000; # debug
}
# Get the last numbers
printtable($lastday);

# DEBUG
#$Validate::Common::cleancountrycache->statistics();

sub processentry {
    return if (!$id);
    # Clean up country, if necessary based on email
    $country = cleancountry($country);
    if (!$country) {
	# note - email2country may return > 2 letters
	$country = substr(email2country($email), 0, 2);
    }
    # Add missing country and 2 versions of unknown country to
    # the "somewhere in the world" count
    if (!$country || $country eq "ZZ" || $country eq "XX") {
	$country = "XW";
    }
    if ($date =~ /^\S+ (\S+) +(\d+).* (\d{4})/) {
	# need to guard against backwards time and other weirdnesses...
	if ($months{$1}) { # don't print on incorrect month
	    $month = sprintf("%4d-%02d", $3, $months{$1});
	    $day = $month . sprintf("-%02d", $2);
	    # note highest day found
	    $lastday = $day if $day gt $lastday;
	    if ($month gt $prevmonth) {
		if ($prevmonth && $month gt $prevmonth) {
		    printtable($day);
		}
		$prevmonth = $month;
	    } elsif ($month lt $prevmonth) {
	#	print STDERR "Ignoring backwards jump from $prevmonth: $date\n";
		++ $backwards;
	    }
	}
    }
    if ($country{$id}) { # was somewhere before
	-- $cc{$country{$id}};
	-- $totcount;
    }
    # If recdate is missing, assume it is a deleted entry
    if (!$hasdata) {
	delete $country{$id};
    } else {
	$country = "XX" if (!$country);
	++ $cc{$country};
	++ $totcount;
	$country{$id} = $country;
    }
    undef $id;
    undef $country;
    undef $recdate;
    undef $email;
}

sub printtable {

    my $label = shift;
    for $cix (sort(keys(%cc))) {
	print "$label:$cix:$cc{$cix}\n";
    }
    print "$label:All:$totcount\n";
    # compute corrected count for dates before the Great Delete
    my $ix = substr($label, 0, 7);
    if ($ix le "2001-11") {
	if (!$corr{$ix}) {
	    warn "No correction for date $ix\n" if $ix gt "1995-09";
	}
	my $real = $totcount - $corr{$ix};
	print "$label:Real:$real\n";
    } else {
	print "$label:Real:$totcount\n";
    }
}

	    
sub getcorrections {
# the way to get the counts is the following statement:
#
# insert into temp_correct select substring(logintime,1,7) as t, count(*)
# from users where state = 'frozen' group by t;
#  insert into temp_correct select substring(logintime,1,7) as t, count(*)
#       from users, frozen where state = 'frozen'
#       and frozen.ftable = 'persons'
#        and frozen.owner = users.f_key group by t;
#
# but the corrections at Apr 02 are probably righter than the corrections later
# and the query takes about a minute of database lock time
# so it is not dynamic

# get some filehandle
    my $users = Counter::open();
    my @corrections = $users->selectany("date, count from temp_correct order by date");
    my $runningsum;
    for my $entry (@corrections) {
	$runningsum += $$entry{count};
	my ($year, $month) = split(/-/, $$entry{date});
	$year += 2; # 2 years offset
	$corr{"$year-$month"} = $runningsum;
    }
    warn @corrections+0, " corrections found\n";
}

    
    
